/**
 * 学生模块数据库操作文件
 */

const { query, pool, queryTrans, format, queryFirst, queryPage } = require('./common');

/**
 * 添加学生
 * @param student 学生信息
 * @param record 档案信息
 * @returns 
 */
const addStudents = async (student, { record, oldSchool }) => {
    const [[s]] = await queryTrans(format(`INSERT INTO students SET ?`, student), format(`UPDATE class_list SET count = count + 1 WHERE id = ?`, [student.class_id]));
    query(`INSERT INTO student_archives_history(student_id,archives_id,remark) VALUES(?,?,?)`, [s.insertId, record, record == 20 ? `此学生${student.join_date}正常入学` : `此学生${student.join_date}从${oldSchool}转入本校`]);
}

/**
 * 修改学生
 * @param student 学生信息
 * @returns 
 */
const updateStudents = (id, student) => query(`UPDATE students SET ? WHERE id = ?`, [student, id]);

/**
 * 修改状态
 * @returns 
 */
const changeStatus = (id, status) => query('UPDATE students SET status = ? WHERE id = ?', [status, id]);

/**
 * 根据身份证号查询是否已存在
 * @param {*} id id
 * @param {*} idNo 身份证号
 * @returns 
 */
const existsByIdNo = (id, idNo) => queryFirst('SELECT 1 n FROM students WHERE (? = 0 OR id <> ?) AND idNo = ?', [id, id, idNo]);

/**
 * 搜索学生
 * @param obj 搜索信息
 * @returns 
 */
const searchStudents = async obj => {
    const conn = await pool.getConnection();
    const { name, idNo, phone, class_id, status, pageNo, pageSize } = obj;
    try {
        const where = conn.format(`WHERE (IFNULL(?,0) = 0 OR s.status = ?) AND (IFNULL(?,'') = '' OR s.name LIKE ?) AND (IFNULL(?,'') = '' OR s.idNo LIKE ?) AND (IFNULL(?,'') = '' OR s.phone LIKE ?) AND (IFNULL(?,0) = 0 OR s.class_id = ?)`,
            [status, status, name, `%${name}%`, idNo, `%${idNo}%`, phone, `%${phone}%`, class_id, class_id]);

        const [[list], [[count]]] = await Promise.all([
            conn.query(`SELECT * FROM(
                    SELECT s.*,IFNULL(c.name,'无') class_name,d.name status_name,ROW_NUMBER() OVER(ORDER BY id)num FROM students s
                    LEFT JOIN class_list c ON s.class_id = c.id
                    INNER JOIN dictionary d ON s.status = d.id
                    ${where}
                    )t WHERE t.num > ? AND t.num <= ?`, [(pageNo - 1) * pageSize, pageNo * pageSize]),
            conn.query(`SELECT COUNT(1) count from students s ${where}`)
        ]);
        return [list, count.count];
    } catch (error) {
        throw error;
    }
    finally {
        conn.release();
    }
}

/**
 * 更改档案
 * @param {*} id 要修改的学生ID
 * @param {*} obj 数据
 */
const changeRecord = (id, obj) => {
    const sqls = [];
    const { record, school, oldSchool, class_id, class_name, date, old_className, old_class_id } = obj;
    switch (record) {
        // 如果是转入
        case 21:
            sqls.push(format(`INSERT INTO student_archives_history(student_id,archives_id,remark) VALUES(?,?,?)`, [id, record, `此学生${date}从${oldSchool}转入至${class_name}`]));
            sqls.push(format(`UPDATE students SET status = 23,class_id = ? WHERE id = ?`, [class_id, id]));
            sqls.push(format(`UPDATE class_list SET count = count + 1 WHERE id = ?`, [class_id]));
            break;
        //调班
        case 22: {
            sqls.push(format(`INSERT INTO student_archives_history(student_id,archives_id,remark) VALUES(?,?,?)`, [id, record, `此学生${date}从${old_className}转入${class_name}`]));
            sqls.push(format(`UPDATE class_list SET count = count - 1 WHERE id = ?`, [old_class_id]));
            sqls.push(format(`UPDATE class_list SET count = count + 1 WHERE id = ?`, [class_id]));
            sqls.push(format(`UPDATE students SET class_id = ? WHERE id = ?`, [class_id, id]));
            break;
        }
        //转出
        case 27:
            sqls.push(format(`INSERT INTO student_archives_history(student_id,archives_id,remark) VALUES(?,?,?)`, [id, record, `此学生${date}转出到${school}`]));
            sqls.push(format(`UPDATE students SET status = 26,class_id = 0 WHERE id = ?`, [id]));
            sqls.push(format(`UPDATE class_list SET count = count - 1 WHERE id = ?`, [old_class_id]));
            break;
        //毕业
        case 28:
            sqls.push(format(`INSERT INTO student_archives_history(student_id,archives_id,remark) VALUES(?,?,?)`, [id, record, `此学生${date}毕业`]));
            sqls.push(format(`UPDATE students SET status = 25 WHERE id = ?`, [id]));
            break;
    }
    return queryTrans(...sqls);
}

/**
 * 添加学生紧急联系人
 * @param {*} obj 紧急联系人
 * @returns 
 */
const addEmergencyContact = obj => query('INSERT INTO student_emergency_contact SET ?', obj);

/**
 * 修改学生紧急联系人
 * @param {*} obj 紧急联系人
 * @returns 
 */
const updateEmergencyContact = (id, obj) => query('UPDATE student_emergency_contact SET ? WHERE id = ?', [obj, id]);

/**
 * 删除学生紧急联系人
 * @param {*} obj 紧急联系人
 * @returns 
 */
const deleteEmergencyContact = id => query('DELETE FROM student_emergency_contact WHERE id = ?', [id]);

/**
 * 获取学生紧急联系人列表
 * @param {*} id 学生ID
 * @returns 
 */
const getEmergencyContacts = id => query('SELECT * FROM student_emergency_contact WHERE student_id = ?', [id]);

/**
 * 获取学生状态
 * @param {*} id 学生ID
 * @returns 
 */
const getStudentStatus = id => queryFirst('SELECT class_id,status FROM students WHERE id = ?', [id]);

/**
 * 搜索学生档案
 * @param {*} obj 搜索对象
 * @returns 
 */
const searchStudentsArchives = obj => {
    const { student_id, record, start_date, end_date } = obj;
    const where = format(`WHERE (IFNULL(?,0) = 0 OR s.archives_id = ?) AND (IFNULL(?,0) = 0 OR s.student_id = ?) AND (IFNULL(?,'') = '' OR s.date >= ?) AND (IFNULL(?,'') = '' OR s.date < ?)`, [record, record, student_id, student_id, start_date, start_date, end_date, end_date]);
    return queryPage(obj, `SELECT * FROM(
        SELECT s.*,t.name student_name,d.name archives_name,ROW_NUMBER() OVER(ORDER BY id DESC)num FROM student_archives_history s
        INNER JOIN students t ON s.student_id = t.id
        INNER JOIN dictionary d ON s.archives_id = d.id
        ${where}
        ) t WHERE t.num > ? AND t.num <= ?`,
        `SELECT COUNT(1) count FROM student_archives_history s ${where}`);
}

/**
 * 添加费用
 * @param {*} obj 
 */
const addFee = obj => query(`INSERT INTO student_fee_history SET ?`, obj);

/**
 * 分页查询费用
 * @param {*} obj 
 */
const searchFee = obj => {
    const { student_id, min_fee, max_fee, type, start_date, end_date } = obj;
    const where = format(`WHERE (IFNULL(?,0) = 0 OR s.student_id = ?) AND (IFNULL(?,0) = 0 OR s.fee >= ?) AND (IFNULL(?,0) = 0 OR s.fee <= ?) 
    AND (IFNULL(?,'') = '' OR s.date >= ?) AND (IFNULL(?,'') = '' OR s.date < ?) AND (IFNULL(?,0) = 0 OR s.type = ?)`, [student_id, student_id, min_fee, min_fee, max_fee, max_fee,
        start_date, start_date, end_date, end_date, type, type]);
    return queryPage(obj, `SELECT * FROM(SELECT s.*,t.name student_name,d.name type_name,ROW_NUMBER() OVER(ORDER BY s.id DESC)num FROM student_fee_history s
        INNER JOIN students t ON s.student_id = t.id
        INNER JOIN dictionary d ON s.type = d.id
        ${where})t
        WHERE t.num > ? AND t.num <= ?`, `SELECT COUNT(1) count FROM student_fee_history s`);
}

module.exports = {
    addFee,
    searchFee,
    addStudents,
    changeRecord,
    existsByIdNo,
    changeStatus,
    updateStudents,
    searchStudents,
    getStudentStatus,
    addEmergencyContact,
    getEmergencyContacts,
    updateEmergencyContact,
    deleteEmergencyContact,
    searchStudentsArchives
}